-- @owner: songjing20
-- @date: 2024-09-25
-- @testpoint: 验证behavior_compat_options参数和游标表达式的关系，有cursor函数,传参不合理，合理报错

--prestep1:切换A库;expect:执行成功
@conn PrimaryDbAmode;
show sql_compatibility;

--stpe1:建表;expect:成功
drop table if exists t_cursor_0017 cascade;
create table t_cursor_0017(employees_id int, department_id int, first_name varchar(50), last_name varchar(50), email varchar(50), phone_number int);

--step2:插入数据;expect:成功
insert into t_cursor_0017 values (1, 1, 'zhang', 'san', '1@abc.com', 123),(2, 1, 'li', 'si', '2@abc.com', 124);
insert into t_cursor_0017 values (1, 1, 'wang', 'wu', '3@abc.com', 321),(2, 1, 'he', 'liu', '4@abc.com', 421);

--step3:创建cursor函数;expect:成功
drop function if exists cursor();
create function cursor(func_name varchar(50)) returns table(first_name varchar(50))as $$
begin
    return query select first_name from t_cursor_0017;
end;
$$ language plpgsql;
/
--step4:behavior_compat_options参数关闭按照cursor函数执行;expect:传参不合理，合理报错
show behavior_compat_options;  -- 默认关闭
select cursor('qwe');
select first_name, cursor('qwe') from t_cursor_0017 d order by first_name;
select first_name, cursor(drop tablet_cursor_0017 d) from t_cursor_0017 d order by first_name;
select first_name, cursor(select * from t_cursor_0017 d) from t_cursor_0017 d order by first_name;
select first_name, cursor((select * from t_cursor_0017 d))from t_cursor_0017 d order by first_name;
select first_name, cursor('with aa as (select employees_id from t_cursor_0017') select * from aa) from t_cursor_0017 d;
select first_name, cursor(with aa as (select employees_id from t_cursor_0017) select * from aa) from t_cursor_0017 d;

--step5:behavior_compat_options参数打开按照cursor函数执行;expect:传参不合理，合理报错
set behavior_compat_options = 'prefer_parse_cursor_parentheses_as_expr';
select cursor('qwe');
select first_name, cursor(alter t_cursor_0017 rename to t_cursor_0017_01) from t_cursor_0017 d order by first_name;
select first_name, cursor(select * from t_cursor_0017 d) from t_cursor_0017 d order by first_name;
select first_name, cursor((select * from t_cursor_0017 d)) from t_cursor_0017 d order by first_name;
select first_name, cursor('with aa as (select employees_id from t_cursor_0017) select * from aa') from t_cursor_0017 d;
select first_name, cursor(with aa as (select employees_id from t_cursor_0017) select * from aa) from t_cursor_0017 d;

--step6:清理环境;expect:成功
drop function if exists cursor();
drop table if exists t_cursor_0017;
